from db.database_manager import DatabaseManager
from typing import Optional, Tuple, List, Any, Union


class BaseDatabaseTools:
    def __init__(self, db_manager: DatabaseManager):
        self.db_manager = db_manager

    def _execute_query(self, query: str, params: Optional[Tuple] = None, fetch: bool = False) -> Union[List[dict], int, bool, None]:
        """执行查询的通用方法"""
        conn = None
        cursor = None

        try:
            # 参数验证
            if not query or not isinstance(query, str):
                raise ValueError("查询语句不能为空且必须是字符串")
            
            conn = self.db_manager.get_connection()
            cursor = conn.cursor()

            if self.db_manager.db_type == "mysql":
                # MySQL 参数占位符是 %s
                formatted_query = query.replace('?', '%s')
                cursor.execute(formatted_query, params or ())
            else:
                # SQLite 参数占位符是 ?
                cursor.execute(query, params or ())

            if fetch:
                if query.strip().upper().startswith('SELECT'):
                    result = cursor.fetchall()
                    # 转换为字典列表
                    if result:
                        if self.db_manager.db_type == "mysql":
                            columns = [desc[0] for desc in cursor.description]
                            return [dict(zip(columns, row)) for row in result]
                        else:
                            return [dict(row) for row in result]
                    return []
                else:
                    # 对于 INSERT 返回最后插入的 ID
                    return cursor.lastrowid if hasattr(cursor, 'lastrowid') else None

            # 非查询操作自动提交
            if not query.strip().upper().startswith('SELECT'):
                conn.commit()

            return True

        except Exception as e:
            # 如果是数据库连接问题，尝试重置连接
            if "Cannot operate on a closed database" in str(e):
                try:
                    self.db_manager.reset_connection()
                except:
                    pass  # 忽略重置连接时的错误
            
            if conn:
                try:
                    conn.rollback()
                except:
                    pass  # 忽略回滚时的错误
            # 添加更多上下文信息到异常中
            raise Exception(f"数据库查询执行失败: {str(e)} (Query: {query})") from e
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass  # 忽略关闭游标时的错误
            # 注意：这里不关闭连接，由连接池管理

    def _execute_transaction(self, operations: List[Tuple[str, Optional[Tuple]]]) -> List[Any]:
        """执行事务操作"""
        conn = None
        cursor = None

        try:
            conn = self.db_manager.get_connection()
            cursor = conn.cursor()

            results = []
            for operation in operations:
                query, params = operation
                
                # 参数验证
                if not query or not isinstance(query, str):
                    raise ValueError("查询语句不能为空且必须是字符串")
                
                if self.db_manager.db_type == "mysql":
                    formatted_query = query.replace('?', '%s')
                    cursor.execute(formatted_query, params or ())
                else:
                    cursor.execute(query, params or ())

                # 如果是查询操作，获取结果
                if query.strip().upper().startswith('SELECT'):
                    result = cursor.fetchall()
                    if result:
                        if self.db_manager.db_type == "mysql":
                            columns = [desc[0] for desc in cursor.description]
                            results.append([dict(zip(columns, row)) for row in result])
                        else:
                            results.append([dict(row) for row in result])
                    else:
                        results.append([])
                else:
                    results.append(cursor.lastrowid if hasattr(cursor, 'lastrowid') else None)

            conn.commit()
            return results

        except Exception as e:
            # 如果是数据库连接问题，尝试重置连接
            if "Cannot operate on a closed database" in str(e):
                try:
                    self.db_manager.reset_connection()
                except:
                    pass  # 忽略重置连接时的错误
                    
            if conn:
                try:
                    conn.rollback()
                except:
                    pass  # 忽略回滚时的错误
            raise Exception(f"事务执行失败: {str(e)}") from e
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass  # 忽略关闭游标时的错误